package com.hippo.core.service;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.hippo.core.util.*;
import com.hippo.core.util.constants.JdbcDriverEnum;
import com.hippo.core.util.sm4.SM4Utils;
import com.jayway.jsonpath.JsonPath;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.sql.Connection;
import java.sql.SQLOutput;
import java.util.*;

/**
 * @author: hippo
 */
@Service
@Slf4j
public class DataGenerateService {

    public static HashMap<String, String> getConnInfoByDbType(JSONObject jsonObject) {
        String dbType = jsonObject.getString("dbType");
        String port = jsonObject.getString("port");
        String dbName = jsonObject.getString("dbName");
        String host = jsonObject.getString("host");
        HashMap<String, String> map = new HashMap<>();
        if (dbType.equals("mysql")) {
            map.put("showTableSql", "select table_name from information_schema.tables where table_schema=? and table_type='BASE TABLE';");
            map.put("showColumnSql", "select COLUMN_NAME,COLUMN_TYPE,COLUMN_COMMENT,IS_NULLABLE,COLUMN_KEY from information_schema.columns where table_name=? and table_schema=? ORDER BY ORDINAL_POSITION");
            map.put("driver", JdbcDriverEnum.MYSQL.driver);
            map.put("url", "jdbc:mysql://" + host + ":" + port + "/" + dbName);
        } else if (dbType.equals("clickhouse")) {
            map.put("showTableSql", "SHOW TABLES from @ where 1 = ?;");
            map.put("showColumnSql", "select distinct name  as COLUMN_NAME,type as COLUMN_TYPE,comment as COLUMN_COMMENT from system.columns where table=? and database=?");
            map.put("driver", JdbcDriverEnum.CLICKHOUSE.driver);
            map.put("url", "jdbc:clickhouse://" + host + ":" + port + "/" + dbName);
        }
        return map;
    }

    public HashMap<String, String> getContactInfo(JSONArray jsonArray) {
        HashMap<String, String> map = new HashMap<>();
        jsonArray.forEach(item -> {
            JSONObject jsonObject = JSONObject.parseObject(JSON.toJSONString(item));
            if (jsonObject.getInteger("isContact") == 1) {
                if (map.get(jsonObject.getString("contactTable")) != null) {
                    map.put(jsonObject.getString("contactTable"),
                            map.get(jsonObject.getString("contactTable")) + "," + jsonObject.getString("contactColumn"));
                } else {
                    map.put(jsonObject.getString("contactTable"), jsonObject.getString("contactColumn"));
                }
            }
        });
        return map;
    }

    public JSONObject generateData(JSONObject jsonObject) throws Exception {
        //生成条数
        int number = jsonObject.getInteger("number");
        //DB链接信息
        JSONObject dbInfo = jsonObject.getJSONObject("dbInfo");
        HashMap<String, String> connInfo = getConnInfoByDbType(dbInfo);
        String driver = connInfo.get("driver");
        String url = connInfo.get("url");
        String username = dbInfo.getString("username");
        String password = dbInfo.getString("password");
        String tableName = dbInfo.getString("tableName");
        DataGenerator fakerData = new DataGenerator();
        JSONArray array = jsonObject.getJSONArray("table");
        HashMap<String, String> contactInfo = getContactInfo(array);
        long startTime12 = System.currentTimeMillis();
        //从数据库连接池中获取数据库连接
        Connection connection = new DruidConnectionPool(driver, url, username, SM4Utils.decryptData_CBC(password)).getConnection();
        //设置不自动提交，以便于在出现异常的时候数据库回滚
        // connection.setAutoCommit(false);
        long endTime12 = System.currentTimeMillis();
        log.info("链接数据库的时间：" + (endTime12 - startTime12) + "ms");
        //构造插入的SQL
        StringBuilder fields = new StringBuilder();
        StringBuilder values = new StringBuilder();
        for (int i = 0; i < array.size(); i++) {
            JSONObject jsonObject1 = array.getJSONObject(i);
            String columnName = jsonObject1.getString("COLUMN_NAME");
            fields.append(i == 0 ? "" : ",").append(columnName);
            values.append(i == 0 ? "" : ",").append("?");
        }
        //SQL前缀
        StringBuilder prefix = DruidConnectionPool.getPrefixByDbType(dbInfo.getString("dbType"), tableName, fields);
        //按数量大小循环插入n
        Object[] bindArgs = new Object[number * array.size()];
        //关联数据
        HashMap<String, List<Map<String, Object>>> contactData = new HashMap<>();
        long prefixStartTime = System.currentTimeMillis();
        for (int n = 0; n < number; n++) {
            HashMap<Object, Object> map = new HashMap<>();
            for (int i = 0; i < array.size(); i++) {
                if (array.getJSONObject(i).getInteger("isContact") == 1) {
                    String contactTable = array.getJSONObject(i).getString("contactTable");
                    String contactColumn = contactInfo.get(contactTable);
                    String sqlText = array.getJSONObject(i).getString("sqlText");
                    if (n == 0 && contactData.get(contactTable + '-' + contactColumn) == null) {
                        try {
                            List<Map<String, Object>> maps = sqlQuery(dbInfo, contactTable, contactColumn, sqlText, number);
                            contactData.put(contactTable + '-' + contactColumn, maps);
                        } catch (Exception e) {
                            return CommonUtil.errorJson("【" + array.getJSONObject(i).getString("COLUMN_NAME") + "】关联其他表的过滤条件有误");
                        }
                    }
                    List<Map<String, Object>> mapList = contactData.get(contactTable + '-' + contactColumn);
                    if (mapList.size() <= 0) {
                        return CommonUtil.errorJson("【" + array.getJSONObject(i).getString("COLUMN_NAME") + "】所关联的其他表字段查询不到数据");
                    }
                    int length = n >= mapList.size() ? mapList.size() - 1 : n;
                    Object obj = mapList.get(length).get(array.getJSONObject(i).getString("contactColumn"));
                    bindArgs[n * array.size() + i] = obj;
                    map.put(array.getJSONObject(i).getString("COLUMN_NAME"), obj);
                } else if (array.getJSONObject(i).getInteger("isContact") == 0) {
                    JSONArray faker = array.getJSONObject(i).getJSONArray("FAKER");
                    String value = array.getJSONObject(i).getString("VALUE");
                    if (faker != null) {
                        if (faker.getString(0).equals("customize.increment")) {
                            if (value.split(",").length != 2) {
                                return CommonUtil.errorJson("字段【" + array.getJSONObject(i).getString("COLUMN_NAME") + "】未按函数要求传参，多个参数的方法，值也需要用英文逗号隔开");
                            }
                            try {
                                long num = Long.parseLong(value.split(",")[0]) + n * Long.parseLong(value.split(",")[1]);
                                bindArgs[n * array.size() + i] = num;
                                map.put(array.getJSONObject(i).getString("COLUMN_NAME"), num);
                            } catch (NumberFormatException e) {
                                return CommonUtil.errorJson("字段【" + array.getJSONObject(i).getString("COLUMN_NAME") + "】生成规则为自增长，输入的值必须是整数");
                            }
                        } else {
                            String params = "";
                            if (faker.size() > 2) {
                                params = faker.getString(2);
                            }
                            try {
                                Object valueByMethodName = fakerData.getValueByMethodName(faker.getString(0), faker.getString(1), params, value);
                                bindArgs[n * array.size() + i] = valueByMethodName;
                                map.put(array.getJSONObject(i).getString("COLUMN_NAME"), valueByMethodName);
                            } catch (Exception e) {
                                e.printStackTrace();
                                return CommonUtil.errorJson(e.getMessage());
                            }
                        }
                    } else {
                        bindArgs[n * array.size() + i] = value;
                        map.put(array.getJSONObject(i).getString("COLUMN_NAME"), value);
                    }
                } else if (array.getJSONObject(i).getInteger("isContact") == 2) {
                    String copyCurrentField = array.getJSONObject(i).getString("copyCurrentField");
                    Object o = map.get(copyCurrentField);
                    if (o == null) {
                        return CommonUtil.errorJson("字段【" + array.getJSONObject(i).getString("COLUMN_NAME") + "】所关联的当前表字段【" + copyCurrentField + "】必须先有值");
                    }
                    if (array.getJSONObject(i).getInteger("isMapping") == 1) {
                        String matchKey = array.getJSONObject(i).getString("matchKey");
                        String valueKey = array.getJSONObject(i).getString("valueKey");
                        try {
                            String mappingJson = JSON.toJSONString(array.getJSONObject(i).getJSONArray("mappingJson"));
                            String text = JsonPath.read(mappingJson, String.format("$..[?(@.%s==\"%s\")].%s", matchKey, String.valueOf(o), valueKey)).toString();
                            Object o1 = JSONArray.parseArray(text).get(0);
                            if (JSONArray.parseArray(text).size() == 0) {
                                return CommonUtil.errorJson("字段【" + array.getJSONObject(i).getString("COLUMN_NAME") + "】所映射的JSON没有匹配的值");
                            }
                            bindArgs[n * array.size() + i] = JSONArray.parseArray(text).get(0);
                            map.put(array.getJSONObject(i).getString("COLUMN_NAME"), JSONArray.parseArray(text).get(0));
                        } catch (Exception e) {
                            return CommonUtil.errorJson("字段【" + array.getJSONObject(i).getString("COLUMN_NAME") + "】所映射的JSON格式不符合要求");
                        }
                    } else {
                        bindArgs[n * array.size() + i] = o;
                        map.put(array.getJSONObject(i).getString("COLUMN_NAME"), o);
                    }
                } else if (array.getJSONObject(i).getInteger("isContact") == 3) {
                    String filePath = array.getJSONObject(i).getString("filePath");
                    List<Map<String, String>> list = ExcelUtil.redExcel(filePath);
                    int length = n >= list.size() ? list.size() - 1 : n;
                    String data = list.get(length).get(array.getJSONObject(i).getString("COLUMN_NAME"));
                    data = data == null ? "" : data;
                    bindArgs[n * array.size() + i] = data;
                    map.put(array.getJSONObject(i).getString("COLUMN_NAME"), data);
                }
            }
            //拼接未替换的SQL语句
            prefix.append(n == 0 ? "" : ",").append(" (").append(values).append(")");
        }
//        log.info("未替换的SQL：" + prefix);
        //导入db，返回新增操作更新条数
        long prefixEndTime = System.currentTimeMillis();
        log.info("生成数据+构建SQL时长：" + (prefixEndTime - prefixStartTime) + "ms");
        try {
            //获取开始时间
            long startTime = System.currentTimeMillis();
            int count = DruidConnectionPool.executeUpdate(connection, prefix.toString(), bindArgs);
            //获取结束时间
            long endTime = System.currentTimeMillis();
            //输出程序运行时间
            log.info("INSERT数据时长：" + (endTime - startTime) + "ms");
            assert count > 0;
            //关闭链接
            if (connection != null) {
                connection.close();
                log.info("database-连接关闭");
            }
            return CommonUtil.successJson("成功新增了" + number + "行，用时:" + (endTime - startTime) + "ms");
        } catch (Exception e) {
            e.printStackTrace();
            return CommonUtil.errorJson(e.getMessage());
        }
    }

    public void exportExcel(JSONObject jsonObject, HttpServletRequest request, HttpServletResponse response) throws Exception {
        int number = jsonObject.getInteger("number");
        JSONObject dbInfo = jsonObject.getJSONObject("dbInfo");
        String tableName = dbInfo.getString("tableName");
        JSONArray array = jsonObject.getJSONArray("table");
        HashMap<String, String> contactInfo = getContactInfo(array);
        DataGenerator fakerData = new DataGenerator();
        //获取所有的标题字段
        String[] titleFields = getTitleFields(array);
        List<Object[]> list = new ArrayList<>();
        //关联数据
        HashMap<String, List<Map<String, Object>>> contactData = new HashMap<>();
        long prefixStartTime = System.currentTimeMillis();
        for (int n = 0; n < number; n++) {
            HashMap<Object, Object> map = new HashMap<>();
            List<Object> list1 = new ArrayList<>();
            //遍历JSONArray,获取字段名称，对应的Faker包路径，Faker方法
            for (int i = 0; i < array.size(); i++) {
                try {
                    String contactTable = array.getJSONObject(i).getString("contactTable");
                    String contactColumn = contactInfo.get(contactTable);
                    String sqlText = array.getJSONObject(i).getString("sqlText");
                    JSONArray faker = array.getJSONObject(i).getJSONArray("FAKER");
                    String value = array.getJSONObject(i).getString("VALUE");
                    Object valueByMethodName;
                    // 关联其他表字段
                    if (array.getJSONObject(i).getInteger("isContact") == 1) {
                        if (n == 0 && contactData.get(contactTable + '-' + contactColumn) == null) {
                            List<Map<String, Object>> maps = sqlQuery(dbInfo, contactTable, contactColumn, sqlText, number);
                            contactData.put(contactTable + '-' + contactColumn, maps);
                        }
                        List<Map<String, Object>> mapList = contactData.get(contactTable + '-' + contactColumn);
//                        int num = new Random().nextInt(mapList.size());
                        int num = n >= mapList.size() ? mapList.size() - 1 : n;
                        valueByMethodName = mapList.get(num).get(array.getJSONObject(i).getString("contactColumn"));
                    }
                    // 不关联
                    else if (array.getJSONObject(i).getInteger("isContact") == 0) {
                        if (faker != null) {
                            if (faker.getString(0).equals("customize.increment")) {
                                if (value.split(",").length != 2) {
                                    valueByMethodName = "字段【" + array.getJSONObject(i).getString("COLUMN_NAME") + "】未按函数要求传参，多个参数的方法，值也需要用英文逗号隔开";
                                }
                                valueByMethodName = Long.parseLong(value.split(",")[0]) + n * Long.parseLong(value.split(",")[1]);
                            } else {
                                //执行Faker方法，传入包名，方法名
                                String params = "";
                                if (faker.size() > 2) {
                                    params = faker.getString(2);
                                }
                                valueByMethodName = fakerData.getValueByMethodName(faker.getString(0), faker.getString(1), params, value);
                            }
                        } else {
                            value = array.getJSONObject(i).getString("VALUE");
                            valueByMethodName = value != null ? value : "";
                        }
                    }
                    // 关联当前表字段
                    else if (array.getJSONObject(i).getInteger("isContact") == 2) {
                        String copyCurrentField = array.getJSONObject(i).getString("copyCurrentField");
                        Object o = map.get(copyCurrentField);
                        if (array.getJSONObject(i).getInteger("isMapping") == 1) {
                            String matchKey = array.getJSONObject(i).getString("matchKey");
                            String valueKey = array.getJSONObject(i).getString("valueKey");
                            try {
                                String mappingJson = JSON.toJSONString(array.getJSONObject(i).getJSONArray("mappingJson"));
                                String text = JsonPath.read(mappingJson, String.format("$..[?(@.%s==\"%s\")].%s", matchKey, String.valueOf(o), valueKey)).toString();
                                if (JSONArray.parseArray(text).size() == 0) {
                                    valueByMethodName = "JSON映射失败";
                                } else {
                                    valueByMethodName = JSONArray.parseArray(text).get(0);
                                }
                                map.put(array.getJSONObject(i).getString("COLUMN_NAME"), valueByMethodName);
                            } catch (Exception e) {
                                valueByMethodName = "mappingJson格式错误";
                            }
                        } else {
                            valueByMethodName = o;
                            map.put(array.getJSONObject(i).getString("COLUMN_NAME"), o);
                        }
                    }
                    // 关联文件
                    else if (array.getJSONObject(i).getInteger("isContact") == 3) {
//                        long startTime = System.currentTimeMillis();
                        String filePath = array.getJSONObject(i).getString("filePath");
                        List<Map<String, String>> list2 = ExcelUtil.redExcel(filePath);
                        int length = n >= list2.size() ? list2.size() - 1 : n;
                        String data = list2.get(length).get(array.getJSONObject(i).getString("COLUMN_NAME"));
                        data = data == null ? "" : data;
                        valueByMethodName = data;
                        map.put(array.getJSONObject(i).getString("COLUMN_NAME"), data);
//                        long endTime = System.currentTimeMillis();
                        //输出程序运行时间
//                        log.info(array.getJSONObject(i).getString("COLUMN_NAME")+"关联文件时长：" + (endTime - startTime) + "ms");
                    } else {
                        valueByMethodName = "";
                    }
                    map.put(array.getJSONObject(i).getString("COLUMN_NAME"), valueByMethodName);
                    list1.add(valueByMethodName);
                } catch (Exception e) {
                    e.printStackTrace();
                    list1.add("");
                }
            }
            list.add(list1.toArray(new Object[0]));
        }
        long prefixEndTime = System.currentTimeMillis();
        log.info("生成数据时长：" + (prefixEndTime - prefixStartTime) + "ms");
        // 告诉浏览器用什么软件可以打开此文件
        response.setHeader("content-Type", "application/vnd.ms-excel");
        // 下载文件的默认名称
        String fileName = "生成" + tableName + "表数据" + number + "条";
        response.setHeader("Content-disposition", "attachment;fileName=" + fileName + ".xlsx");
        response.setHeader("FileName", URLEncoder.encode(fileName, "utf-8"));
        response.setHeader("Access-Control-Expose-Headers", "FileName");
        response.setContentType("application/octet-stream;charset=utf-8");
        OutputStream outPutStream = response.getOutputStream();
        ExportExcel exportExcel = new ExportExcel(tableName, titleFields, list);
        exportExcel.export(outPutStream);
        outPutStream.flush();
        outPutStream.close();
    }

    public JSONObject testGenerate(JSONObject jsonObject) throws Exception {
        DataGenerator fakerData = new DataGenerator();
        int isContact = jsonObject.getInteger("isContact");
        if (isContact == 1) {
            String contactTable = jsonObject.getString("contactTable");
            String contactColumn = jsonObject.getString("contactColumn");
            String sqlText = jsonObject.getString("sqlText");
            JSONObject dbInfo = jsonObject.getJSONObject("dbInfo");
            int contactTableCount = jsonObject.getInteger("contactTableCount");
            List<Map<String, Object>> maps = sqlQuery(dbInfo, contactTable, contactColumn, sqlText, contactTableCount);
            if (maps.size() > 0) {
                int num = new Random().nextInt(maps.size());
                return CommonUtil.successJson(maps.get(num).get(contactColumn));
            }
            return CommonUtil.successJson("关联当前库表查询不到数据");
        } else if (isContact == 0) {
            String packageName = jsonObject.getString("packageName");
            String methodName = jsonObject.getString("methodName");
            String params = jsonObject.getString("params");
            String value = jsonObject.getString("value");
            try {
                if (packageName.equals("customize.increment")) {
                    if (value.split(",").length != 2) {
                        return CommonUtil.errorJson("字段【" + jsonObject.getString("COLUMN_NAME") + "】未按函数要求传参，多个参数的方法，值也需要用英文逗号隔开");
                    }
                    try {
                        long num = Long.parseLong(value.split(",")[0]) + Long.parseLong(value.split(",")[1]);
                        return CommonUtil.successJson(num);
                    } catch (NumberFormatException e) {
                        return CommonUtil.errorJson("输入的值必须是整数");
                    }
                }
                Object valueByMethodName = fakerData.getValueByMethodName(packageName, methodName, params, value);
                return CommonUtil.successJson(valueByMethodName);
            } catch (Exception e) {
                log.error(e.toString());
                return CommonUtil.errorJson(e.getMessage());
            }
        } else if (isContact == 3) {
            String filePath = jsonObject.getString("filePath");
            List<Map<String, String>> list = ExcelUtil.redExcel(filePath);
            int num = new Random().nextInt(list.size());
            String data = list.get(num).get(jsonObject.getString("COLUMN_NAME"));
            data = data == null ? "" : data;
            return CommonUtil.successJson(data);
        } else {
            return CommonUtil.errorJson("关联当前表内字段不建议测试，请选择被关联的字段测试");
        }
    }

    public List<Map<String, Object>> sqlQuery(JSONObject jsonObject, String contactTable, String contactColumn, String sqlText, int num) throws Exception {
        int length = Math.min(num, 10000);
        HashMap<String, String> connInfo = getConnInfoByDbType(jsonObject);
        String driver = connInfo.get("driver");
        String url = connInfo.get("url");
        String sql = "SELECT " + contactColumn + " FROM " + contactTable + " " + sqlText + String.format(" limit %d", length);
        log.info("测试的SQL：" + sql);
        String[] arr = {""};
        List<Map<String, Object>> maps = DruidConnectionPool.executeQuery(driver, url, jsonObject.getString("username"), SM4Utils.decryptData_CBC(jsonObject.getString("password")), sql, arr);
        return maps;
    }

    public String[] getTitleFields(JSONArray array) {
        List<String> rowName = new ArrayList<>();
        for (int i = 0; i < array.size(); i++) {
            String columnName = array.getJSONObject(i).getString("COLUMN_NAME");
            rowName.add(columnName);
        }
        return rowName.toArray(new String[0]);
    }

}
